﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.AnalysisServices.AdomdClient;

namespace SSASUtilities
{
    public class SSASUtil
    {
        public SSASUtil() { }

        public String GetGreetingMsg() { return "Hello SSAS!"; }

        public string GetDatabases(string serverName)
        {
            if (serverName + "" == "") return "<Databases></Databases>";

            string xmlReturn = "<Databases>";

            using (AdomdConnection conn = new AdomdConnection("DataSource=" + serverName))
            {
                // Open the connection
                conn.Open();

                if (conn.State == ConnectionState.Open)
                {
                    //List available databases
                    DataSet catalogs = conn.GetSchemaDataSet(AdomdSchemaGuid.Catalogs, null);
                    DataTable schemaTable = catalogs.Tables[0];
                    foreach (DataRow row in schemaTable.Rows)
                    {
                        xmlReturn += "<Database Name = \"";
                        xmlReturn += row["CATALOG_NAME"].ToString();
                        xmlReturn += "\"></Database>";
                    }
                }

                //Close the connection
                conn.Close();
            }

            xmlReturn += "</Databases>";

            return xmlReturn;
        }

        public string GetCubes(string serverName, string dbName)
        {
            if (serverName + "" == "" || dbName + "" == "") return "<Cubes></Cubes>";
            
            string xmlReturn = "<Cubes>"; ;

            //Connect to the local server
            using (AdomdConnection conn = new AdomdConnection("Data Source=" + serverName + ";Database=" + dbName))
            {
                // Open the connection
                conn.Open();

                if (conn.State == ConnectionState.Open)
                {
                    //Loop through every cube
                    foreach (CubeDef cube in conn.Cubes)
                    {
                        //Skip hidden cubes.
                        if (cube.Name.StartsWith("$"))
                            continue;
                        xmlReturn += "<Cube Name = \"";
                        xmlReturn += cube.Name;
                        xmlReturn += "\"></Cube>";
                    }
                }

                //Close the connection
                conn.Close();
            }

            xmlReturn += "</Cubes>";

            return xmlReturn;
        }

        public string GetKPIs(string serverName, string dbName, string cubeName)
        {
            if (serverName + "" == "" || dbName + "" == "" || cubeName + "" == "") return "<KPIs></KPIs>";

            string xmlReturn = "<KPIs>"; ;

            //Connect to the local server
            using (AdomdConnection conn = new AdomdConnection("Data Source=" + serverName + ";Database=" + dbName))
            {
                // Open the connection
                conn.Open();

                if (conn.State == ConnectionState.Open)
                {
                    //Loop through every cube
                    foreach (CubeDef cube in conn.Cubes)
                    {
                        //Skip unmatched cubes.
                        if (cube.Name != cubeName)
                            continue;

                        xmlReturn += "<Cube Name = \"";
                        xmlReturn += cube.Name + "\" ";
                        xmlReturn += "LastUpdated = \"";
                        xmlReturn += cube.LastUpdated.ToString() + "\" ";
                        xmlReturn += "LastProcessed = \"";
                        xmlReturn += cube.LastProcessed.ToString() + "\"></Cube>";

                        foreach (Kpi kpi in cube.Kpis)
                        {
                            AdomdCommand myKPICommand = new AdomdCommand();
                            myKPICommand.Connection = conn;
                            myKPICommand.CommandText = @"SELECT { KPIValue(@Value), KPIGoal(@Goal), KPIStatus(@Status), KPITrend(@Trend) } ON COLUMNS FROM [" + cube.Name + "]";
                            myKPICommand.Parameters.Clear();
                            myKPICommand.Parameters.Add(new AdomdParameter("Value", kpi.Name));
                            myKPICommand.Parameters.Add(new AdomdParameter("Goal", kpi.Name));
                            myKPICommand.Parameters.Add(new AdomdParameter("Status", kpi.Name));
                            myKPICommand.Parameters.Add(new AdomdParameter("Trend", kpi.Name));

                            // Execute query
                            CellSet cellset = myKPICommand.ExecuteCellSet();

                            try
                            {
                                string kpiValue = cellset.Cells[0].FormattedValue;
                                string kpiGoal = cellset.Cells[1].FormattedValue;
                                string kpiStatus = cellset.Cells[2].FormattedValue;
                                string kpiTrend = cellset.Cells[3].FormattedValue;

                                xmlReturn += "<KPI Name = \"";
                                xmlReturn += kpi.Name + "\" ";
                                xmlReturn += "Value = \"";
                                xmlReturn += kpiValue + "\" ";
                                xmlReturn += "Goal = \"";
                                xmlReturn += kpiGoal + "\" ";
                                xmlReturn += "Status = \"";
                                xmlReturn += kpiStatus + "\" ";
                                xmlReturn += "Trend = \"";
                                xmlReturn += kpiTrend + "\" ";
                                xmlReturn += "Description = \"";
                                xmlReturn += kpi.Description.ToString() + "\" ";
                                xmlReturn += "StatusGraphic = \"";
                                xmlReturn += kpi.StatusGraphic.ToString() + "\" ";
                                xmlReturn += "TrendGraphic = \"";
                                xmlReturn += kpi.TrendGraphic.ToString() + "\"></KPI>";
                            }
                            catch (AdomdErrorResponseException myException)
                            {
                            }
                        }
                    }

                    //Close the connection
                    conn.Close();
                }

                xmlReturn += "</KPIs>";

                return xmlReturn;
            }
        }
    }
}
